<?php
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

namespace LEA\UserBundle\SQL\other;

use Doctrine\DBAL\Connection;

class QueriesAuth{
	private $connection;

    public function __construct(Connection $dbalConnection)  {
        $this->connection = $dbalConnection;
    }

	function doCheckStudentRole($user){
	     $query = "select etudCle from etudiant where etudCle like '".$user."' ;";
	     $stmt = $this->connection->prepare($query);
	     $stmt->execute();

	     if ($stmt===FALSE)
	        return FALSE;
	     else {
	     	$cle=  $stmt->fetch();
	     	return $cle['etudCle'];
	     }
	 }

	 function doCheckProfRole($user){
	     $query = "select profCle from fil_dept.membre where profCle like '".$user."' and fonction not like 'secretaire';";
	     $stmt = $this->connection->prepare($query);
	     $stmt->execute();

	     if ($stmt===FALSE)
	        return FALSE;
	     else {
	     	$cle= $stmt->fetch();
	     	if($cle)
	        	return TRUE;
	        else
	        	return FALSE;
	     }
	 }

	 function doCheckSecrRole($user){
	     $query = "select profCle from fil_dept.membre where profCle like '".$user."' and fonction like 'secretaire';";
	     $stmt = $this->connection->prepare($query);
	     $stmt->execute();

	     if ($stmt===FALSE)
	        return FALSE;
	     else {
	     	$cle= $stmt->fetch();
	     	if($cle)
	        	return TRUE;
	        else
	        	return FALSE;
	     }
	 }
	 function checkRoles($user, $session)
	    {
	        if (!$session->has('REF_YEAR')) {
	          $session->set('REF_YEAR', date("Y"));
	        }

	        $query = "Select roleRef from roles_membres"
	                        ." where userRef='".$user."' and roleRef in (select roleCle from roles) "
	                        ." and anneeReference<=".$session->get('REF_YEAR')." and obsolete=0;";

			$stmt = $this->connection->query($query);

				while  ($row = $stmt->fetch() )
				{
					//echo($row['roleRef']." ") ;

					$session->set('CK_ROLES', $session->get('CK_ROLES').$row['roleRef']." ");

				}

				$queryString = "Select roleRef from roles_membres"
								." where userRef='".$user."' "
								." and anneeReference<=".$session->get('REF_YEAR')." and obsolete=0;";

				$stmt = $this->connection->prepare($query);
	     		$stmt->execute();

				if($stmt===TRUE){
					while  ($row = $stmt->fetch()){
						$this->checkRoles($role['roleRef']);
					}
				}
	    }
}
?>
